Map Data to Elements

Primary and Foreign Keys

If a Dataset has a Primary Key with a data type of int and its Identity property set to True or a data type of uniqueidentifier (GUID), Form Studio will automatically assign a value to the Primary Key field before writing a record to the database. You do not need to account for the Primary Key value within your form.

For Primary Key(s) which have other data types, including int with Identity set to False, you will need to manually enter a suitable value into the Primary Key field(s), either within custom JavaScript or via an output mapping from a suitable Element on the form, before you submit the form or update the physical database from an Element's Event properties or within custom JavaScript. When you publish your form, Form Studio will warn you if there are any Datasets which won't automatically have their Primary Keys generated.

Digitise Forms Datasources automatically have their Primary Key set to be a GUID, so this is generally only a potential issue with Imported Datasources. You can check a Primary Key's data type by opening the relevant Dataset from the Datasources tab in the Project Explorer.

If you have two Datasets which are related by a single Foreign Key field and which have no other Foreign Key relationships, by default Digitise Forms will also automatically handle matching the Foreign Key field in the child Dataset to the Primary Key value in the corresponding parent Dataset's record. In this case, you don't need to account for values in the Foreign Key field either, Digitise Forms will populate these automatically with the correct value from the Parent's Primary Key. In addition, child Datasets will automatically be uploaded within the parent data, when the parent Dataset is uploaded; you don't need to upload child Datasets separately. You can, however, disable this relationship within your form, if you want to, in which case you will need to manually populate the Foreign Key field with the appropriate value and, if necessary, provide for all Datasets, both parent and children, to be uploaded separately.

If you have multiple Datasets with Foreign Key relationships, providing they only have one Foreign Key relationship each and all child Foreign Keys are linked to the same Parent Primary Key, Digitise Forms will also manage Foreign Key values and data uploads as just described.

In other situations, e.g. where you have Datasets which have multiple Foreign Key fields, you will need to manually handle the values entered into the Foreign Key fields when writing records to the database and, if you are not using a Submit button (or Capita Pay 360 Cart Element configured to submit the form when payment is made) you will need to include a mechanism in your form to update each Dataset separately.

If you add one or more Recordset Element to a form and then use the New Datasource from Form option (discussed in the next section below) to create the Datasets for these Recordset, each Dataset will automatically include a Foreign Key field to link it to the rest of the form data. As with other parent-child relationships, Digitise Forms will handle populating this Foreign Key value and manage data uploads as described above.

You can also use this automatic data management where you have one or more Recordsets mapped to Datasets in an imported Datasource where all the relevant Datasets have the same Foreign Key relationship with the same parent Dataset.

When you are downloading data to the form, you can also use Foreign Key relationships to include child records within a parent Dataset's data and to link the child records to the Primary Key value of the current record in the parent Dataset.

In this scenario, when a record in the parent Dataset becomes the current record, Digitise Forms will automatically load child records where the Foreign Key field matches the new current record’s Primary Key into the child Dataset, making them available to the form. Providing you have configured suitable input mappings to the Elements on your form, the appropriate Elements will also be updated with data from the child Dataset.

As with writing records to the database, you can configure whether child data should be managed with the parent data in this way or not and this automatic data management requires each child Datasets to have only one Foreign Key relationship and for that Foreign Key relationship to be with the same parent Dataset.

In other situations, e.g. where you have Datasets which have multiple Foreign Key fields or you turn off automatic handling of child data, you will need to handle the download of data, moving through records and setting the current record separately for each Dataset, both parent and child Datasets.

For more information about handling Datasets with Foreign Key relationships, see Handling Datasets with Foreign Keys.

Create a New Database to store Data from your Form

The New Datasource from Form feature provides a quick and easy way to create a new Datasource to hold the information entered onto a form by your form users, where you don't already have a database you want to use to hold some or all of the information entered. The function reads all the Elements on your form and automatically generates suitable database table structures to store any form data which you haven't already configured to be output mapped to another Datasource. When you next publish the form after running New Datasource from Form, these database tables will be created by Form Studio in your specified instance of SQL Server.

New Datasource from Form uses the Project Datasource to store your form data. Running the option will add the Project Datasource under the Datasources tab in the Project Explorer Pane if it doesn't exist already, and then create a Dataset within that Datasource containing a data item for all Elements on the form, except Recordset Elements, which don't already have an output mapping configured. It will then automatically output map each included Element to its respective data item.

If you want New Datasource from Form to include data from Recordset Elements within the Project Datasource as well, you will need to define the table structure for each Recordset you want to include BEFORE you run New Datasource from Form. New Datasource from Form will then add a separate Dataset to the Project Datasource for each of your included Recordsets and output map the Recordsets to their respective Datasets. You can specify a table structure for a Recordset using the Define static columns option in the Recordset Builder pop-up accessed from the Recordset's Value property - for more details refer to Map Recordset Elements later in this topic.

Using this option is the simplest way to create a Datasource to save the data entered into your form when a user submits the form.

See Create a Digitise Forms Datasource for more information.

Manually Map Elements

You can manually map Elements to data items within the Element properties.

This method cannot be used to map data to Recordset Elements. For information about mapping Recordset Elements see the relevant section later in this topic.

To display an Element's properties, select the Element within the Form Design workspace. The selected Element's properties will be displayed in the Properties Pane.

Data mappings are normally handled under the Data tab, Picture showing the Data Tab. .

If an Element doesn't have a Data tab within its properties, the Element generally cannot be mapped to a Datasource. There are some exceptions to this - Link, Label, Paragraph and Title Elements can all be input mapped but the properties are found under the Appearance tab, Picture showing the Appearance tab. .

Data mapping properties can be recognised by the presence of a mapping button, Picture showing the Mapping Button. . Some Elements have a larger version of this button, e.g.:

Picture showing larger version of Mapping Button.

This larger button is used with Check List, Drop List, Radio List and Recordset Elements and is described in the relevant sections below.

Some Elements have only one mappable value, e.g. a Text Box Element can only contain a single text string, but others may have more than one, e.g. a Map Element may include coordinates to indicate the opening location on the map, the position of a pin, zoom level and so on.

To map a property, click on its mapping button, Picture showing the Mapping Button. .

The Mapping Builder pop-up will be displayed:

Picture showing the Mapping Builder Popup.

At the top of the pop-up, you will see the name of the property which is being mapped.

Below this are the Input and Output mapping options. Some properties allow data mappings in both directions, in which case you will see options for both as in the picture above. Some properties only allow mapping in one direction, in which case only the allowable mapping will be shown.

If both input and output mappings are available, you can specify just one or both according to your requirements and, where you specify both directions, you can define the same or different mappings for each direction.

In addition, an input mapping may allow you to specify a fixed, or static, value instead of mapping the Element's input to a Datasource.

In the above image, an Is Visible in PDF checkbox is displayed just beneath the Output mapping option. This feature allows a mapped element to be included in the PDF version of the form. Note that for this to work correctly, the Store Request Data option also needs to be selected within the Publishing Profile for the Datasource to which the element you wish to display is mapped. For more information, see the Publish Your Form and Create PDF Copies of Completed Forms topics.

For Elements which can't be edited at runtime, such as Label, Link or Title Elements, you can use the static value to specify the text to be displayed where the text is fixed and won't change. For Link Elements, you can use static values to specify either or both of the link text displayed on the form and the address of the web page to load when a user clicks on the link at runtime. Alternatively, for a Link Element, either or both of these can be read from a database.

For Elements which can be edited at runtime, such as Text Boxes, a static value allows you to enter text to be used when the form loads to display an initial value or determine the initial setting or selection of the Element.

If you perform a form reset within a custom JavaScript any static values will be redisplayed. For editable Elements, a user may overwrite a static value when filling in the form, but if they don't the static value will be used as the output value if the Element has an output mapping specified.

To specify a Datasource mapping, make sure the Type field of the relevant direction is set to Datasource.

Click in the Datasource field to drop-down a list of Datasources defined in this project. Choose the Datasource containing the data item you want to map to this property.

You cannot output map an Element to an Imported Stored Procedure Datasource which returns a Result Set nor can you input map an Element to an Imported Stored Procedure Datasource which doesn't.

Next, click on the Dataset field and then choose the Dataset containing the data item you want to map to this property.

You cannot output map an Element to a read-only Dataset, i.e. a Dataset which has no Primary Key. If you select a Datasource under the Output mapping section of the Mapping Builder pop-up which has any read-only Datasets, these will not be included in the Dataset list.

Finally, click on the Field option and choose the required data item to map to this property.

If you specify an input mapping for an Address Finder, Complex Name, Text Box or Text Panel Element, the mapping will be displayed in the Element within the Form Design workspace, in the format {<datasource>.<dataset>.<dataitem>}, and will replace any placeholder text defined in the element's properties. For example the following Text Box has its placeholder text replaced with a defined input mapping:

Changes to the placeholder text in the properties won't be reflected within the Form Design workspace, unless you remove the input mapping, in which case any specified placeholder text will be displayed again:

If you specify an input mapping for a Label, Paragraph or Title Element or for a Link Element's Text property, the mapping will be displayed in place of the text and the in-line editor will be disabled, since you don't need to be able to edit the text as this will be supplied from the datasource. For example, the following Paragraph Element has an input mapping defined:

You may need to reload the form to display the input mapping. The mapping is only shown in Form Studio and isn't displayed on the published form.

To specify a Static Value for an input mapping, click on the Type field and choose Static Value. The Datasource options will be replaced by a Value field:

Picture showing Static Value Mapping for a Form Item.

Enter the relevant value in the edit box. For example, for a Text Box you can enter a string of characters to be displayed in the Text Box and for a Drop List you can enter the value to be selected in the list when the form loads. For a Checkbox the default values are true or false to specify whether the box should be checked or not, respectively. However, if you have changed the Checked Value or Unchecked Value properties under the Advanced tab, Picture showing the Advanced tab. , for a Checkbox, the value you enter as your static value must match the appropriate value in these properties.

Each direction has a Reset button, which will reset the options back to default for the individual direction. The pop-up Reset button, in the bottom left-hand corner of the pop-up, will reset all options within the pop-up.

When you have finished, click on the Save button, to save your changes or on the Cancel button to abandon your changes.

If you save your mapping(s), the mapping(s) will be shown in the Element's properties:

Picture showing saved Mappings.

The picture above shows a property for a Text Box Element which allows both input and output mappings. Remember, a static value is shown with no icon, an input mapping will have a Picture showing Input-Mapped icon. icon to the left and an output mapping will have a Picture showing Output-Mapped icon. icon. If a property only allows mapping in one direction, only one edit box will be displayed instead of two.

Provide Display Values for Check List, Drop List and Radio List Elements

Check Lists, Drop Lists and Radio Lists all allow you to display a predefined list of items from which a user can select when filling in the form.

The list of possible items is supplied by mapping the Element to a Datasource containing the required items. You can read the items in from an existing Imported Datasource, store them in the Project Datasource or create a new Digitise Forms Datasource for this purpose.

Whichever Datasource you use, you will need to map the Element to that Datasource in the Element's properties. If you will be using the Project Datasource or a Digitise Forms Datasource to store the values, Form Studio allows you to define the database and enter the list items and will then create the database table when you publish the form, you don't need to create the Datasource separately. Note, however, that you can include images in your list and if you want to do this you will need to input the images into the database outside of Form Studio, e.g. using a SQL script.

An important point to note about Check Lists, Drop Lists and Radio Lists is that each item on the list has two, sometimes three, properties.

The first property is called the Label property and contains a descriptive text string to be displayed in the list. This is the text that a user will see displayed on the form.

The second property is the Value property. This property holds a data value which represents the item. The value and label can be the same for an item or can be different. For example, you could be providing a list of marital statuses with labels such as 'Married', 'Divorced', 'Separated' etc. The data values could be the same, i.e. 'Married', 'Divorced', 'Separated' etc. or different, e.g. 'Married' could be represented by a data value of 0, 'Divorced' by 1, 'Separated' by 2 and so on because these are how these items are represented in your back office systems' database(s). The label is used only to display the item in the list, everywhere else an item is referred to by its value, e.g. when outputting the current selection, it is the value which will be output not the label.

The third property is only supported with Check Lists and Radio Lists. This optional property, the Image property, can hold an image to be displayed alongside, or in place of, the label.

If you want to display a mock-up of a populated Check List or Radio List within the Form Design workspace, you configure this under the Value property as well.

To add a list of items to a Check List, Drop List or Radio List:

Add the appropriate Element to your form.

Select the Element to display its properties in the Properties Pane.

Click on the Data Tab, Picture showing the Data tab. .

This tab has two properties - Value and List Items:

The Value Property

This property contains the current selection. For Drop Lists and Radio Lists you can only select one item at a time. Check Lists, however, allow you to have multiple selections, in which case the selected items are listed in a single line separated by a specified separator character, by default a '|' character. The separator character can be changed using the Separator property, located under the Advanced tab, Picture showing the Advanced tab. .

The Value property's input mapping allows you to provide an initial selection for the list. You can use an input mapping to a Datasource to obtain the selection from the current record or you can specify the required selection in a Static Value. In either case, the value read from the Datasource or specified in the Static Value must match exactly the value property of the item to be selected. When the Element is displayed, if an input mapping is specified, the value will be read from the Datasource's current record (providing the Datasource has been loaded) or from the Static Value and matched against the listed items. If there is a match, the matching item in the list will be selected. For a Check List, the value can contain multiple selections in a single string separated by the Element's specified separator character, e.g. using our earlier marital status example, it is unlikely that you would want to select more than one item at once but, to illustrate the point, the string 0| 1| 2 would select 'Married', 'Divorced' and 'Separated' in the list.

The Value property's output mapping will output the current selection when the form is submitted. If the user has changed the selection from your initial value(s), the user-selected value(s) will be output. For a Check List, if multiple items have been selected, the selected items will be output in a single string with the items separated by the separator character.

It is the value from the Value property of the item, or items, selected which will be written to the submission record if you specify an output mapping for the Element. So in our example, if a user selects 'Married' from the list, when the form is submitted it will actually be a 0 which is output and not 'Married'.

Likewise, when specifying an input mapping or Static Value, you must use the Value property of the item rather than the Label property to specify the item(s) to be selected.

The Element's Value property is mapped as described above under Manually Map Elements.

The List Items Property

This property maps to a Datasource containing a record for each item to be displayed in the list. When filling in the form, the user will be able to make their selection from the items read in from the Datasource. This property only has an input mapping since it is only used to provide the items that make up the list.

This property allows you to create a new database table to store your list items or to map to an existing database table which already holds the required information for this list. If you create a new database table, you can also enter the items you want in the list, although, as mentioned earlier, you cannot enter images into the database within Form Studio. The database table must contain a separate record for each item in the list.

In addition, this property allows you to configure a mock up of a populated Check List or Radio List Element to display on your form within the Form Design workspace whilst you are designing your form to give you an idea of what the Element will look like when the form is used .

All three list Element types allow each item in the list to have a Label and a Value property.

The Labels are the descriptions usually displayed on the form as the list items, i.e. these are the text strings that the user will see and choose from, e.g. 'Married', 'Divorced', 'Widowed' etc.

The Values allow you to specify the values that will represent each item within the data read in from an input mapping and output to the submission record when you submit the form. This allows you to have different data values than the Label strings, e.g. you might want to output 0 to represent 'Married', 1 for 'Divorced', 2 for 'Widowed' etc. because these are the codes used by your back office systems. If you don't need separate display and output values, the Label and Value properties for each item can be the same.

In addition, Check Lists and Radio Lists also allow have an Image property, allowing you to provide an image for each item to be displayed alongside or in place of the Label. Images are optional.

After defining a new database table or specifying a mapping to an existing table, you then need to map the columns in this database table to the list properties. This involves specifying which column in the database table holds the Labels, which the Values and, for Check Lists and Radio Lists which include images, which holds the Images.

When a form is submitted, the currently selected item in a list Element is output via the ELEMENT's Value property (see earlier in this topic). The output value is derived from the Values column of the List Items database table. In the case of a Check List, multiple selections are output in a single string separated by a separator character specified in the Separator property, located under the Advanced tab, Picture showing the Advanced tab. .

To specify your list items, click on the List Items button, Picture showing Larger Mapping button. .

The Recordset Builder pop-up will be displayed with the Structure tab selected:

Picture showing Recordset Builder Popup with Structure tab selected.

This pop-up allows you to specify the database table containing your list items. You can define a new database table (Create a new dataset option), which will be created when you next publish the form, or map to an existing database table (Map to an existing datasource option), which already contains the data you want to use for your list.

Create a new dataset

This option allows you to create a Dataset to be used to hold the list items for the Element. You can use this option to create a new table within the Project Datasource or to create a new Digitise Forms Datasource and Dataset to hold your items.

By default, Form Studio suggests using the Project Datasource and creating a Dataset with the name: <element name>Items.

If you want to use a different Datasource, edit the Datasource field and enter the new name. If the Picture showing the Close button. button is displayed at the end of the edit box, you will need to clear the field by clicking on this button before you can make changes to the Datasource name.

If you want to use a different Dataset, type the name you want to use for the Dataset in the Dataset field. You cannot use an existing Dataset to store your values and a new Dataset will always be created.

Click on the Create button.

The Recordset Builder pop-up will change to show the Mappings tab:

Picture showing Recordset Builder popup with Mappings tab selected.

The Mapping Direction option is automatically set to Input. Remember, you cannot specify an output mapping here as this property is only used to read in the list of items.

Below this the remaining options allow you to map the columns in your new Dataset to the list's Label, Value and Image properties.

The Dataset created for a Check List or Radio List is given three columns to match the list properties - Label, Value and Image. For a Drop List the Dataset is created with only two columns as it doesn't support images - Label and Value.

By default the Dataset columns are mapped to the corresponding named list properties. So the Label column is mapped to the Label property, which means that the Element will take it's Labels from the Label column in the Dataset, list Values will be taken from the Value column and, for Check Lists and Radio Lists, Images will be taken from the Image column.

You can leave these default mappings as they are, if this fits the data you want to use in your list.

But what if you don't need or want separate descriptions and values?, i.e. using the same example as before, you want a marital status of 'Married' to be output as 'Married' rather than as a code, such as 0. In this case, you can either map both the Label and Value properties to the same Dataset column, e.g. set both Label and Value properties to Label, or you can enter the same data values in both Label and Value columns when you create the data.

If you are configuring data mappings for a Check List or Radio List and you don't want to use images with this list, you can clear the Image property mapping by clicking on the Picture showing the Close button. button to the right of the option. This means that this Element will not use images, but won't remove the image column from the database so that you could use the same data to populate another Check List or Radio List but where you do want to include images in that listing.

If you want to change the column names within the Dataset, you will first need to edit the Dataset from the Project Explorer's Datasources tab. You can then return to the Element's properties and select the columns you want to act as your Label, Value and Image properties here under the Mappings tab.

When you have finished configuring the Dataset, click on the Save button to save your changes. If you want to abandon any changes at this point, click on the Cancel button instead.

The Recordset Builder pop-up will disappear.

In the Properties Pane, the List Items property will now display the Dataset mapping:

Picture showing List Items property with Dataset mapping shown.

You can redisplay the Recordset Builder pop-up by clicking on the property's Picture showing Mappings button. button. The pop-up will appear with the Mappings tab displayed. If you want to return to the Structure tab, you will have to clear the property's value or click on the Reset button within the Mappings tab.

You can now enter your list items. Note that the Dataset will not be created nor have the items' data stored in it until you next publish your form.

To add your list items:

Click on the List Items property's Picture showing the List Items button. button.

Your List Items Dataset will be opened in the Form Design workspace:

Picture showing list item values Dataset open in the Forms Design Workspace.

If you are configuring a Check List or Radio List and you don't want to include images, you can remove the Image column by unchecking the checkbox to the left of the column name. Alternatively, you can leave it included, e.g. you may want to use the same database to populate other Check Lists or Radio Lists (maybe in the future on different forms) and some of those you may want to use images. If you are configuring a Drop List, the Image column will not be included.

If you include the Image column, do not uncheck the Allow Null property for this column if you want to specify data to be added to the new database, otherwise Form Studio won't be able to create the database and you will get an error message displayed when you try to publish your form.

Click on the Data tab:

Picture showing the Mappings Data tab.

The Data table allows you to enter the data which will form the items to be displayed in the list. Each item in your list should be entered as a separate row within this table.

Ignore the Id column. This column provides a unique identifier for each row in the table and will be completed automatically when the table is created.

Click in the Label column of the first row to enter the label for the first item in your list. If you only want to display pictures in your list without a description, leave this column blank.

Then click in the Value column to input a data value for this item. If you aren't using separate descriptions and data values for your items and you mapped the Label column to both the list's Label and Value properties, leave this column blank. If you don't have separate descriptions and data values but you have different Dataset columns specified for the list's Label and Value properties, enter the same value in this column as you entered in the Label column.

If you want to have different descriptions and values, enter the required value for the description entered in the Label column.

If you want to add images to your list, you will need to add them to the database table later after it has been created. The table will not be created until you next publish your form. For now simply enter the Label and Value columns and then publish your form to create the table. You then need to add your images outside Form Studio, e.g. by using a SQL query within Microsoft SQL Server Management Studio. If you don't want to display any pictures, and you didn't remove the Image column earlier, just leave this column blank.

Press Return to add a new row or press the Tab key to move to the Image column and then again to add a new row.

Enter the data for your next item.

Continue adding items in this way until you have added them all.

If you want to go back and edit any entries, click in the cell you want to edit or use Tab , Shift+Tab and Arrow keys to move between columns and rows.

To delete an item from the list, delete the row in the Data table. To do this, click in the first column of the row you want to delete. A black triangle will be displayed in this column, indicating this is the currently selected row. Press Del, or right-click and choose Delete Row, to delete the currently selected row.

When you have finished creating your list items, you can return to editing your form by choosing one of the form's pages from the Pages tab in the Project Explorer.

The next time you publish your form, your new Dataset will be created and your list items added to it.

Map to an existing datasource

If you have an existing database table which contains the items you want to display in a Check List, Drop list or Radio List Element, you can use that table to populate the Element at runtime.

The database will need to have one record per item to be included in your list and each record must include at least one column which contains the labels for the items making up your list.

If you are working with data where the labels and the data values are different, e.g. you have list items 'Married', 'Divorced', Separated' etc. but these need to be output as 0, 1, 2 etc., you will need a second column, in each record, to provide the corresponding data value for the label.

In the case of Check Lists and Radio Lists, a third optional column can be available to provide images to be displayed with, or instead of, the item labels. If the Label column is empty for a list item, only the picture will be displayed within the list.

Before you can map the database to your list Element, you will first need to create a Datasource and Dataset for it, if you haven't already done so.

If you have to create your Datasource and Dataset, once you have done that, you will need to follow the instructions earlier in this topic until you display the Recordset Builder pop-up again:

Picture showing the Recordset Builder Popup.

Within the Map to an existing datasource section, select the required Datasource from the Datasource drop-down list and then the relevant Dataset from the Dataset drop-down.

Click on the Save button below the Dataset option to save your mappings.

The Reset button will clear the current settings and return the pop-up to its default values. The Cancel button will close the pop-up without saving your changes.

The Recordset Builder pop-up will change to show the Mappings tab:

Picture showing the Recordset Builder Popup with Mappings tab selected.

The Mapping Direction option is automatically set to Input. Remember, you cannot specify an output mapping here as this property is only used to read in the list of items.

Below this the remaining options allow you to map the columns in your specified Dataset to the list's Label, Value and Image properties.

For Check Lists and Radio Lists, all three list properties will be included. For Drop Lists only Label and Value properties will be shown.

For each of the list properties that you want to use, you need to specify which column in your Dataset contains the relevant data for that property.

Click on each of the drop-downs in turn and select the required column from the list of data items.

If you only want to display pictures in your list, leave the Label property blank.

If you aren't working with data where the item's labels are different to the values, either leave the Values property blank or select the same column for this property as you did for the Label property.

If you won't be using images in a Check List or Radio List leave the Image property blank.

If you make a mistake, simply click on the relevant drop-down and select the correct column. Alternatively, you can clear a selection by clicking on the Picture showing the Close button. button to the right of the drop-down list.

When you have finished, click on the Save button to save your changes. If you want to abandon any changes at this point, click on the Cancel button instead.

The Recordset Builder pop-up will disappear.

In the Properties Pane, the List Items property will now display your Dataset mapping:

Picture showing the List Items property.

You can redisplay the Recordset Builder pop-up by clicking on the property's Picture showing the Mappings button. button. The pop-up will appear with the Mappings tab displayed. If you want to return to the Structure tab, you will have to clear the property's value or click on the Reset button within the Mappings tab.

Display a Mock-up of a Check List or Radio List Element in the Workspace

The Options tab allows you to configure mock-ups of Check Lists or Radio Lists to be displayed on your form whilst you are designing the form within the Form Design workspace. This allows you to display a representation of the Element in Design Mode to give you an idea of what the form will look like at runtime, e.g.:

Mock-up of a Check List to display on your form whilst designing the form.

The Options tab allows you to specify a text string to be displayed to represent a row within the list and the number of rows you want to display:

Picture showing the Recordset Builder Popup.

In the Placeholder text option specify a text string to be displayed in the workspace as list or column items and then in the Number of placeholders option specify the number of rows you want to display. In our example image of a Check List above, the Placeholder text was specified as "List Item" and the Number of placeholders as 3.

Although you can configure the Options tab for Drop Lists as well, the options are ignored and don't display anything.

When you have finished configuring the Display options, you will need to click on the Save button to save your changes and then reload the form to display the mock-up of the Element on the form.

Map Recordset Elements

The Recordset Element provides a table allowing you to list multiple records from a single Dataset with the option to allow users to edit the data in the list.

You can provide both input and output mappings for a Recordset but, where you specify both directions, you must use the same Dataset for both.

In most cases, you would use a Recordset Element to display and/or input a set of data which is related to some or all of the other data on the form but where the user may want to see or input more than one instance of this data set.

For example, let's suppose you are creating a form for your HR department to record occupational health assessments and they want to display and/or input periods of absence due to illness for the staff member being assessed. You cannot know how many periods of absence an individual staff member will have had but your form needs to be able to handle multiple instances. A Recordset Element is ideal here because it will allow the form to deal with as many instances of absence as necessary.

One way to achieve this would be to use the New Dataset from Form option to create a Digitise Forms Datasource to store your data. To do this, you add your Recordset Element to the form along with the other Elements you need and then use New Dataset from Form to create two Datasets in the Project Datasource to store your data. The main Dataset, or parent, would be named after the form and would contain the non-Recordset data. The second Dataset, or child, would be named after the Recordset and would contain the data entered into the Recordset. When using the form the user would be able to enter a record for each period of absence into the Recordset table, allowing them to input multiple separate periods, if required. When they submit the form, the non-Recordset data would be stored as a single record in the parent Dataset with a record ID to uniquely identify the record. The Recordset data would be stored as multiple records, one for each period of absence, in the child Dataset, linked to the parent data by the inclusion of the parent record ID as a Foreign Key field in each child record.

Another way to handle this would be to use imported database tables - one holding the parent data and a second holding the child data absence records. As with the Digitise Forms Datasource approach, each record in the child table would need to be related back to the parent record using a Foreign Key field. You would then map your Recordset to the child Dataset and allow the child Dataset to be managed by the parent Dataset.

Generally, the Recordset Element is intended to be used to hold child data of some sort, but you can use it for non-dependent data if you want to, using an imported Datasource. If you do this, however, the data in the Recordset will not be related to the rest of the form's data, unless the Dataset it is mapped to contains a field which can be used for this purpose and you use custom JavaScript to add an appropriate value into this field before the form's data is submitted.

To map data to a Recordset, first select the Recordset in the Form Design workspace.

The selected Element's properties will be displayed in the Properties Pane.

Click on the Data Tab, Picture showing the Data tab. .

The Value property allows you to define the composition of each row in the Recordset Element and specify a mock-up Recordset to display on your form whilst designing it.

Click on the property's Picture showing the larger Mappings button. button.

The Recordset Builder pop-up will be displayed:

Picture showing Recordset Builder popup.

Define static columns

This option allows you to manually define the columns you want to display in your table.

If you use this method, once you have defined the structure of the table, you will then need to create a database which has the same record structure to store data from the table, using the New Datasource from Form option. Until you create the output mapping table the Element will not work if you publish the form.

Note, also, that you must have at least one other output-mappable Element on your form besides the Recordset, if you want to use this method to define the data structure for the Recordset. This is because separate Datasets are created for each Recordset, allowing users to add multiple rows to the Recordset whilst filling in the form, and each Dataset has to be linked back to the form, which is only done when New Datasource from Form creates a Dataset for non-Recordset Elements on the form.

Click on the New... button.

The Recordset Builder will change to show the Mappings tab:

Picture showing Recordset Builder popup displaying Mappings tab.

By default, the table is configured for output mapping only, which will display an empty table in your finished form for users to input data to. You can input map the table as well, if you want the table to display data when the form loads, by selecting the Input option and entering the required data under the Datasources tab (see below for more details). You can't configure an input mapping only if you are defining static columns; to do this you would need to map the Element to an existing database.

To add a column to the table, click on the Add column button.

A new column will be added:

Picture showing adding a new column under the Recordset Builder popup's Mappings tab.

Add as many more columns as you want in the same way:

Picture showing Recordset Builder popups Mappings tab with multiple columns added to Dataset.

You can change the name of a column by clicking on the name you want to change and editing it in the usual Windows manner. Note that column names cannot contain spaces, non-alphanumeric characters apart from '_' and cannot start with a number.

By default, each new column is given a data type of String. You can change this if required, e.g. if you want the column to be a date or number, using the drop-down list to the right of the Column Name field.

Picture showing Recordset Builder popups Mappings tab with changed Data Type for last column.

If you want to remove a column, use the Picture showing the Close button. button at the end of the row to delete it.

The columns will be displayed in the Recordset in the order they are listed here, with the top most column being first on the left.

When you have specified all the required columns, click on the Metadata tab to configure the columns further:

Picture showing Recordset Builder popup's Metadata tab.

You can widen the columns by hovering your mouse pointer over a column divider in the column headings row until the mouse pointer changes to a double-headed arrow and then clicking and dragging the column border. Whilst working in this table, you can change the order the rows are displayed in by clicking on a column heading to sort by that column. Clicking on a column heading again, will reverse the order of the rows. The order of the rows here doesn't affect the order in which columns will be displayed in the Recordset.

When you have finished setting up the columns for this Recordset, click on the Save button to save your changes. If you want to lose your changes instead, click on the Cancel button to close the pop-up without saving.

The Reset button will clear the current settings and return the pop-up to its default values.

If you save your column settings, the Value property in the Properties Pane will change to:

Picture showing the Value property.

You can redisplay the Recordset Builder pop-up by clicking on the property's Picture showing the Mappings button. button. The pop-up will appear with the Mappings tab displayed and you can edit the column settings on this tab and also on the Metadata tab. If you want to return to the Structure tab, however, you will have to clear the property's value or click on the Reset button to clear the current settings.

After defining the columns for a Recordset, you will need to create a Dataset to be used to store information entered into the table at runtime . To do this you need to use the New Datasource from Form option. This option is used to automatically create Datasets to store data from the form when the form is submitted. As part of this functionality, New Datasource from Form will create a separate Dataset for each Recordset you have on your form that is input mapped to static columns, and automatically output map the Recordsets to the appropriate Datasets.

If you want to input map a Recordset using static columns, you will need to supply the data to be read in to the Recordset. You can only do this after you have created a Dataset for the Recordset, i.e. once you have run New Datasource from Form. Click on the Datasources tab in the Project Explorer and then on the Project Datasource. The Datasets for the Project Datasource will be displayed in the Form Design workspace. Double-click on the relevant Dataset and then choose the Data tab. You can now enter data to be read into the table in the column grid.

If you add one or more Recordset Element to a form and then use the New Datasource from Form option (discussed in the next section below) to create the Datasets for these Recordset, each Dataset will automatically include a Foreign Key field to link it to the rest of the form data. As with other parent-child relationships, Digitise Forms will handle populating this Foreign Key value and manage data uploads as described above.
You can also use this automatic data management where you have one or more Recordsets mapped to Datasets in an imported Datasource where all the relevant Datasets have the same Foreign Key relationship with the same parent Dataset.

Map to an existing datasource

This option allows you to display and update data in an existing remote database through a Recordset Element.

Before you can map the database to the Element, you will need to create a Datasource to specify the connection details and then a Dataset identifying the columns from the database that you want to display in this Recordset. Note, however, that if you include more columns within the Dataset than you want for this Recordset, you can hide individual columns later, so they won't be displayed at runtime.

In the Map to an existing datasource section of the Recordset Builder pop-up's Structure tab choose the required Datasource and Dataset from the drop-down lists and then click on the Save button immediately below the dataset option to save your changes.

The Recordset Builder pop-up will change to show the Mappings tab:

Picture showing Recordset Builder popup's Mappings tab for an existing database.

The Mappings tab shows the columns contained within your chosen Dataset.

By default the Recordset will be configured for both input and output mappings. You can deselect either of these if you won't be using them. Note, however, that you cannot map a Recordset to different input and output Datasources. If your chosen Dataset is a read-only Dataset, because it has no Primary Key, you can't map an output to it and the output mapping option won't be selected.

The columns will be displayed in the Recordset in the order in which the properties appear listed here. The top most property will be the first column from the left.

To remove a column from the Recordset click on the Picture showing the Close button. button at the end of its row. You will also need to hide the column on the Metadata tab (see below).

Click on the Metadata tab to configure the columns further:

Picture showing Recordset Builder popup's Metadata tab for an existing database.

You can widen the columns by hovering your mouse pointer over a column divider in the column headings row until the mouse pointer changes to a double-headed arrow and then clicking and dragging the column border. Whilst working in this table, you can change the order the rows are displayed in by clicking on a column heading to sort by that column. Clicking on a column heading again, will reverse the order of the rows. The order of the rows here doesn't affect the order in which columns will be displayed in the Recordset.

When you have finished mapping the recordset to the database, click on the Save button to save your changes. If you want to lose your changes instead, click on the Cancel button to close the pop-up without saving.

The Reset button will clear the current settings and return the pop-up to its default values.

If you save your mapping settings, the Value property in the Properties Pane will change to:

Picture showing the Value property.

You can redisplay the Recordset Builder pop-up by clicking on the property's Picture showing the Mappings button. button. The pop-up will appear with the Mappings tab displayed and you can edit the column settings on this tab and also on the Metadata tab. If you want to return to the Structure tab, however, you will have to clear the property's value or click on the Reset button to clear the current settings.